package com.sx.service;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.dfec.utils.DruidPlugin;


public class RealService {
//	两种连接池切换
//	QueryRunner qr=new QueryRunner(DataSourceUtils.getDatasSource());
	QueryRunner qr=new QueryRunner(DruidPlugin.getDataSource());
	List<Object> list=null;
	
	public List<Object> getNearPosition(String lat,String lon) throws Exception{
		list=new ArrayList<>();
		StringBuilder sb=new StringBuilder();
		  sb.append("select  *,round(6378.138*2*asin(sqrt(pow(sin(("+lat+"*pi()/180-lat*pi()/180)/2),2)+cos("+lat+"*pi()/180)*cos(lat*pi()/180)*pow(sin(("+lon+"*pi()/180-Lon*pi()/180)/2),2)))*1000)/1000 ")
			.append(" as juli from cmiss_all_position_hours ")
			.append(" where Datetime=(select MAX(Datetime) from cmiss_all_position_hours) ORDER BY juli ASC LIMIT 1");
//		System.out.println(sb.toString());
		List<Map<String, Object>> result = qr.query(sb.toString(), new MapListHandler());
		list.add(result);
		return list;
	}

	
	//任意时次降水量统计  根据1小时降水自己统计
	public List<Object> getRain(String cnty, String count, String rain, String starttime, String sendtime) throws SQLException {
		list=new ArrayList<>();
		StringBuilder sb=new StringBuilder();
		  sb.append("select a.* from ( select sum(pre_1h) as rain,station_name as s_name,cnty")
			.append("  from cmiss_all_position_hours where cnty= '"+cnty+"'")
			.append("  and Datetime BETWEEN '"+starttime+"' and  '"+sendtime+"'")
			.append("  group by station_name order by rain desc   limit 0,"+count)
			.append(") as a where a.rain>= "+rain);
//		System.out.println(sb.toString());
		List<Map<String, Object>> result = qr.query(sb.toString(), new MapListHandler());
		list.add(result);
		return list;
	}
    /**
     * 
     * @param cnty 县
     * @param count 请求数量
     * @param rain  雨量过滤条件
     * @param time  时次 1,3,6,12,24
     * @return  根据时次获取本地向前推8小时
     * @throws SQLException
     */
	public List<Object> getRain(String cnty, String count, String rain, String time) throws SQLException {
		list=new ArrayList<>();
		StringBuilder sb=new StringBuilder();
		  sb.append("select PRE_"+time+"h as rain,")
			.append(" Station_Name as s_name,cnty,Datetime ")
			.append(" from cmiss_all_position_hours")
			.append(" where cnty='"+cnty+"'  and  PRE_"+time+"h >="+rain)
			.append("  and Datetime BETWEEN  date_add(NOW(), interval -"+(Integer.parseInt(time)+8)+" hour) and  NOW() ")
			.append(" GROUP BY Station_Name ")
			.append(" ORDER BY rain desc LIMIT 0,"+count);
		  
		System.out.println(sb.toString());
		
		List<Map<String, Object>> result = qr.query(sb.toString(), new MapListHandler());
		list.add(result);
		return list;
	}
	
//
	public List<Object> getRain(String station_name) throws SQLException {
		list=new ArrayList<>();
		StringBuilder sb=new StringBuilder();
		  sb.append("select PRE_1h as rain,")
			.append(" Station_Name as s_name,cnty,datetime ")
			.append(" from cmiss_all_position_hours")
			.append(" where station_name='"+station_name+"' order by datetime desc");
		List<Map<String, Object>> result = qr.query(sb.toString(), new MapListHandler());
		list.add(result);
		return list;
	}
//	
	
	
}
